﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System.IO;
using Newtonsoft.Json.Linq;

namespace ToJson
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable dt;
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                Application.DoEvents();
                openFileDialog1.Filter = "Excel XLSX|*.xlsx|Excel2007|*.xls";
                openFileDialog1.ShowDialog();
                textBox1.Text = openFileDialog1.FileName;
                comboBox1.DataSource = GetExcelSheets(openFileDialog1.FileName);
                comboBox1.DisplayMember = "TABLE_NAME";
                comboBox1.ValueMember = "TABLE_NAME";
                //InitData();
                button2.Enabled = true;
                //button2_Click(null,null);
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
           
        }

        public DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName)
        {
            string strConn = GetConStr(FileFullPath);
            
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            //DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn); 

            DataTable dtSourceDataTemp = new DataTable();
            odda.FillSchema(dtSourceDataTemp, SchemaType.Source);
            odda.Fill(dtSourceDataTemp);

            DataTable dtSourceData = new DataTable();
            odda.FillSchema(dtSourceData, SchemaType.Source);

            //for (int i = 0; i < dtSourceDataTemp.Columns.Count; i++)
            //{
            //    if (dtSourceDataTemp.Columns[i].DataType == typeof(double))
            //    {
            //        DataRow row = dtSourceDataTemp.Rows[0];
            //        if ((Double)row[i]*1000%1000 > 0)
            //        {
                        
            //        }
            //        else
            //        {
            //            dtSourceData.Columns[i].DataType = typeof(int);
            //        }
            //    }
            //}

            odda.Fill(dtSourceData);
            //DataRowCollection row = dtSourceData.Rows;
            //odda.Fill(ds);
            conn.Close();
            return dtSourceData;
        }
        public  DataTable GetExcelSheets(string FileFullPath)
        {
            string strConn = GetConStr(FileFullPath);
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            // 得到包含数据架构的数据表
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            conn.Close();
            return dt;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            Application.DoEvents();
            string result = JsonConvert.SerializeObject(dt, new DataTableConverter());
            richTextBox1.Text = result;
            ParseJson();
            string str = textBox1.Text;
            str = str.Substring(str.LastIndexOf(@"\")+1);
            str = str.Substring(0, str.LastIndexOf(@"."));
            str = PinyinHelper.GetPinyin(str);
            str += "_";
            str += PinyinHelper.GetPinyin(comboBox1.Text.Replace("$", ""));
            StreamWriter sw = new StreamWriter(str + ".json", false, Encoding.UTF8);
            sw.Write(richTextBox1.Text);
            sw.Close();
            sw.Dispose();
            System.Diagnostics.Process.Start(Application.StartupPath);
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //InitData();
        }
        private void InitData()
        {
            dt = GetExcelToDataTableBySheet(textBox1.Text, comboBox1.Text);
            //dt = ds.Tables[0];
            dataGridView1.DataSource = dt;
        }
        public string GetConStr(string FileFullPath)
        {
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此連接可以操作.xls與.xlsx文件HDR=YES第一行为列名
            if (checkBox1.Checked)
            {
                strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; //此連接只能操作Excel2007之前(.xls)文件
            }
            return strConn;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            ParseJson();
        }
        public void ParseJson()
        {
            try
            {
                Application.DoEvents();
                if (richTextBox1.Text.Contains("[")&&richTextBox1.Text.Contains("]"))
                {
                    var ja = JArray.Parse(richTextBox1.Text);
                    richTextBox1.Text = ja.ToString();
                }
                else
                {
                    var jo = JObject.Parse(richTextBox1.Text);
                    richTextBox1.Text = jo.ToString();
                }
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            InitData();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            Application.DoEvents();
            string result = JsonConvert.SerializeObject(dt, new DataTableConverter());
            richTextBox1.Text = result;
            ParseJson();
            string str = textBox1.Text;
            str = str.Substring(str.LastIndexOf(@"\") + 1);
            str = str.Substring(0, str.LastIndexOf(@"."));
            str = PinyinHelper.GetPinyin(str);
            str += "_";
            str += PinyinHelper.GetPinyin(comboBox1.Text.Replace("$", ""));
            StreamWriter sw = new StreamWriter(str + ".json", false, Encoding.UTF8);
            sw.Write(result);
            sw.Close();
            sw.Dispose();
            System.Diagnostics.Process.Start(Application.StartupPath);
        }
    }
}
